import openpyxl

# 1.workbook
workbook = openpyxl.Workbook()  # 新建workbook 可读写 自带sheet Sheet1
# workbook = openpyxl.Workbook(write_only=True) 只写

# 2.sheet
sheet = workbook.active
sheet_new = workbook.create_sheet('data', index=1)  # index 0 开始

workbook.remove(sheet_new)  # 删除sheet 或 del workbook[sheet_new.title]
sheet.title = 'sheet新名'  # 赋值改名

# 3.cell
sheet['A2'] = 'dog'  # 直接赋值
num = 3
for cell in sheet['A1':'C1']:
    for c in cell:
        c.value = num
        num += 2
sheet['D1'] = '=AVERAGE(A1:C1)'  # 平均值 读取加上data_only=True这样读到的返回的就是数字

# 4 append 添加一行数据
row1 = [1, 2, 3, '=AVERAGE(A1:C1)']
sheet.append(row1)

rows = [
    ['Number', 'data1', 'data2'],
    [2, 40, 30],
    [3, 40, 25],
    [4, 50, 30],
    [5, 30, 10],
    [6, 25, 5],
    [7, 50, 10],
]
column_list = list(zip(
    *rows))  # 转置 [('Number', 2, 3, 4, 5, 6, 7), ('data1', 40, 40, 50, 30, 25, 50), ('data2', 30, 25, 30, 10, 5, 10)]
for c in column_list:
    sheet.append(c)

# 5.格式
from openpyxl.styles import Font, colors, Alignment
# 字体
font = Font(name='等线', size=24, italic=True, color=colors.BLACK, bold=True)
# 对齐方式
align = Alignment(horizontal='center', vertical='center')
sheet['A1'].font = font
sheet['B3'].alignment = align
# 第1列列高
sheet.column_dimensions['A'].width = 40
# 第一行行高
sheet.row_dimensions[1].heiht = 30
# 合并单元格
sheet.merge_cells('A1:B2')
sheet['A1'].alignment = align # 合并后只可以往左上角写入数据，也就是区间中:左边的坐标。
sheet.unmerge_cells('A1:B2')

# 保存
workbook.save(r'../a_datas/workbook.xlsx')
